from app import create_app
from app.libs.error_code import NotFound
from flask import abort, current_app
from datetime import datetime

from flask_sqlalchemy import SQLAlchemy as _SQLAlchemy, BaseQuery, Pagination
from sqlalchemy import inspect, Column, Integer, SmallInteger, orm
from contextlib import contextmanager



# Now you can use the engine to connect to your database
# with engine.connect() as connection:
#     result = connection.execute("SELECT 'Hello, world!'")
#     for row in result:
#         print(row)

# Stop the SSH tunnel
# server.stop()



class SQLAlchemy(_SQLAlchemy):
    @contextmanager
    def auto_commit(self):
        try:
            yield
            self.session.commit()
        except Exception as e:
            db.session.rollback()
            raise e


class Query(BaseQuery):
    def filter_by(self, **kwargs):
        if 'status' not in kwargs.keys():
            kwargs['status'] = 1
        return super(Query, self).filter_by(**kwargs)

    def filter_all(self, **kwargs):
        r"""apply the given filtering criterion to a copy
        of this :class:`.Query`, using keyword expressions.

        e.g.::

            session.query(MyClass).filter_by(name = 'some name')

        Multiple criteria may be specified as comma separated; the effect
        is that they will be joined together using the :func:`.and_`
        function::

            session.query(MyClass).\
                filter_by(name = 'some name', id = 5)

        The keyword expressions are extracted from the primary
        entity of the query, or the last entity that was the
        target of a call to :meth:`.Query.join`.

        .. seealso::

            :meth:`.Query.filter` - filter on SQL expressions.

        """

        return super(Query, self).filter_by(**kwargs)

    def get_or_404(self, ident):
        rv = self.get(ident)
        if not rv:
            raise NotFound()
        return rv

    def first_or_404(self, msg=None):
        rv = self.first()
        if not rv:
            raise NotFound(msg)
        return rv


db = SQLAlchemy(query_class=Query)


class Base(db.Model):
    __abstract__ = True
    create_time = Column('create_time', Integer)
    status = Column(Integer, default=1)

    def __init__(self):
        self.create_time = int(datetime.now().timestamp())

    def __getitem__(self, item):
        return getattr(self, item)

    @property
    def create_datetime(self):
        if self.create_time:
            return datetime.utcfromtimestamp(self.create_time + 28800).strftime("%Y-%m-%d %H:%M:%S")
        else:
            return None

    def set_attrs(self, attrs_dict):
        for key, value in attrs_dict.items():
            if hasattr(self, key) and key != 'id':
                setattr(self, key, value)

    def delete(self):
        self.status = int(datetime.now().timestamp())

    def hard_delete(self, commit=False):
        db.session.delete(self)
        if commit:
            db.session.commit()

    def keys(self):
        return self.fields

    def hide(self, *keys):
        for key in keys:
            self.fields.remove(key)
        return self

    def append(self, *keys):
        for key in keys:
            self.fields.append(key)
        return self


class Page(object):
    """Internal helper class returned by :meth:`BaseQuery.paginate`.  You
    can also construct it from any other SQLAlchemy query object if you are
    working with other libraries.  Additionally it is possible to pass `None`
    as query object in which case the :meth:`prev` and :meth:`next` will
    no longer work.
    """

    def __init__(self, total, items):
        #: the unlimited query object that was used to create this
        #: pagination object.
        #         #: the current page number (1 indexed)
        self.code = 0
        #: the number of items to be displayed on a page.
        self.msg = ''
        #: the total number of items matching the query
        self.count = total
        #: the items for the current page
        self.data = items


class MixinJSONSerializer:
    @orm.reconstructor
    def init_on_load(self):
        self._fields = []
        # self._include = []
        self._exclude = []

        self._set_fields()
        self.__prune_fields()

    def _set_fields(self):
        pass

    def __prune_fields(self):
        columns = inspect(self.__class__).columns
        if not self._fields:
            all_columns = set(columns.keys())
            self._fields = list(all_columns - set(self._exclude))

    def hide(self, *args):
        for key in args:
            self._fields.remove(key)
        return self

    def keys(self):
        return self._fields

    def __getitem__(self, key):
        return getattr(self, key)
